<?php
/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/7/31
 * Time: 9:39
 */

namespace app\index\controller;


use think\Cache;
use think\Db;
use app\api\model\OrderModel;
use phpcvs\Csv;
use think\Request;

class Export extends Backend
{


    public function order_list()
    {
        $param   =Request::instance()->get();
        $relation=db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node    =db('role')->where('id', $relation['role_id'])->find();
        $xlsCell=['签约时间', '订单编号', '录入时间', '城市', '店长姓名', '店铺', '物业', '管家', '小区', '录入人员', '微信', '渠道', '二级渠道', '订单状态', '房屋问题', '二级分类', '客户名称', '客户电话', '项目地址', '开工时间', '完工时间', '签单金额', '收款金额'];
        $list   =db('order')
            ->join('contract', 'contract.orders_id = order.order_id', 'left')
            ->join('chanel', 'order.channel_id=chanel.id', 'left')
            ->join('channel_details', 'order.channel_details=channel_details.id', 'left')
            ->join('user', 'user.user_id = order.assignor', 'left')
            ->join('goods_category', 'goods_category.id = order.pro_id', 'left')
            ->join('goods_category goods_category1', 'goods_category1.id = order.pro_id1', 'left')
            ->join('personal personal', 'order.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('community', 'community.id = order.order_id', 'left')
            ->join('city', 'city.city_id = order.city_id', 'left')
            ->join('county', 'county.county_id = order.county_id', 'left')
            ->join('product', 'product.product_id = order.product_id', 'left')
            ->join('store', 'store.store_id=user.store_id', 'left')
            ->join('group_community group', 'order.group_community=group.group_id', 'left')
            ->join('complaint', 'complaint.order_id = order.order_id', 'left');
        if (!empty($param['order_store']) && !empty($param['order_store'])) {
            $list->where('user.store_id', intval($param['order_store']));

        }

        if (!empty($param['channel_id']) && !empty($param['channel_id'])) {
            $list->where('order.channel_id', intval($param['channel_id']));

        }
        if (!empty($param['city_id']) && !empty($param['city_id'])) {
            $list->where('order.city_id', intval($param['city_id']));

        }
        if (!empty($param['channel_details']) && !empty($param['channel_details'])) {
            $list->where('order.channel_details', intval($param['channel_details']));

        }
        if (!empty($param['assignor']) && !empty($param['assignor'])) {
            $list->where(['user.username'=>['like', "%{$param['assignor']}%"]]);

        }
        if (!empty($param['residential_id']) && !empty($param['residential_id'])) {
            $list->where(['group.username|group_community.username' => ['like', "%{$param['residential_id']}%"]]);

        }
        if (!empty($param['start_time']) && !empty($param['end_time'])) {
            $start=strtotime($param['start_time'] . ' 00:00:00');
            $end  =strtotime($param['end_time'] . ' 23:59:59');
            $param['order_timeType']=isset($param['order_timeType'])? $param['order_timeType'] : 2;
            switch (intval($param['order_timeType'])) {
                case 1://录入时间
                    $list->whereBetween('order.created_time', [$start, $end]);
                    break;
                case 2://签约时间
                    $list->whereBetween('contract.con_time', [$start, $end]);
                    $list->whereBetween('order.state', [4, 7]);
                    break;
                case 3://开工时间
                    $list->whereBetween('order.start_time', [$start, $end]);
                    break;
                case 4://完工时间
                    $list->whereBetween('order.finish_time', [$start, $end]);
                    break;
                case 5://完款时间
                    $list->whereBetween('order.received_time', [$start, $end]);
                    break;
                case 6://结算时间
                    $list->whereBetween('order.cleared_time', [$start, $end]);
                    break;

            }
        } else {
            $list->whereBetween('order.state', [4, 7]);
        }
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $list->where(['store.store_id'=>$this->admin['store_id']]);
            }
            if (!empty($this->admin['qu'])) {
                $list->whereIn('order.channel_id', $this->admin['qu']);
            }
            if (!empty($this->admin['residential_id'])) {

                $list->where(function ($query) {
                    $query->whereIn('order.group_community', $this->admin['residential_id'])->whereOr(['group_community.group_id' => ['in', $this->admin['personal_id']]]);
                });



            }else{
                $node['role_name']='我'.$node['role_name'];
                if(strpos($node['role_name'],'物业')>=0 && strpos($node['role_name'],'物业')) {
                    $list->where('order.group_community', '0000001');
                }
            }
        }
        $data=$list->where('user.ce', 'neq', 2)->field('order.*,goods_category.title as category_title,goods_category1.title as category_title1,city.city,county.county,product.product_id as product,complaint.order_id as complaint,community.residential_id,community.management,user.username,user.mobile,user.store_id,contract.con_time,channel_details.title as channel_details_title,chanel.title as chanel_title,store.store_name,group.username as residential_names,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')->select();

        foreach ($data as $k=>$v) {
            $ues[$k]['signing_time']         =intval($v['con_time']) > 0? date('Y-m-d', $v['con_time']) : '';
            $ues[$k]['order_no']         =$v['order_no']. "\t";
            $ues[$k]['created_time']         =intval($v['created_time']) > 0? date('Y-m-d', $v['created_time']) : '';
            $ues[$k]['city']                 =$v['city'];
            $ues[$k]['user_name']            =$v['username'];
            $ues[$k]['store']                =$v['store_name'];

            if ($v['channel_id'] == 35 && ($v['channel_details'] == 118 || $v['channel_details'] == 120|| $v['channel_details'] == 119) && $v['tui_jian'] != 0) {
                $ues[$k]['residential_name']=$v['usernames'];
                $ues[$k]['management']=$v['username2'];
                $ues[$k]['residential_quarters']=$v['usernames1'];
            }else{
                $ues[$k]['residential_name']     =$v['residential_names'];
                $ues[$k]['management']           =$v['management'];
                $ues[$k]['residential_quarters'] =$v['residential_quarters'];
            }
            $ues[$k]['entry']                =$v['entry'];
            $ues[$k]['wechat']               =intval($v['wechat']) == 1? '已加' : '未加';
            $ues[$k]['chanel_title']         =$v['chanel_title'];
            $ues[$k]['channel_details_title']=$v['channel_details_title'];
            $ues[$k]['state']                =OrderModel::type($v['state']);;
            $ues[$k]['order_question'] =$v['category_title'];
            $ues[$k]['category_title1']=$v['category_title1'];
            $ues[$k]['contacts']       =$v['contacts'];
            $ues[$k]['mobile']         =$v['telephone'] . "\t";
            $ues[$k]['address']        =$v['city'] . $v['county'] . $v['addres'];
            $ues[$k]['start_time']     =intval($v['start_time']) > 0? date('Y-m-d', $v['start_time']) . "\t" : '';
            $ues[$k]['finish_time']    =intval($v['finish_time']) > 0? date('Y-m-d', $v['finish_time']) . "\t" : '';
            //计算成交额
            $turnover=(new OrderModel())->newOffer($v['order_id'])['amount'];
            //计算收款额度
            $payment=\app\api\model\Common::order_for_payment($v['order_id']);
            $payment=$payment[0] + $payment[1];
            //计算增项金额
//            $increment=db('capital')
//                ->where('ordesr_id', $v['order_id'])
//                ->where('types', 1)
//                ->where('enable', 1)
//                ->where('increment', 1)
//                ->sum('to_price');
            //人工费
            //实际人工
//            $artificial=order_for_reality_artificial([['order_id'=>$v['order_id']]]);
            //实际材料
//            $material=order_for_reality_material([['order_id'=>$v['order_id']]]);
            //费用报销
//            $outlay=order_for_outlay([['order_id'=>$v['order_id']]]);
            //利润
//            $lirun                    =$turnover - $artificial - $material - $outlay;
//            $data[$k]['payment_ratio']=0;
//            $data[$k]['lirun_ratio']  =0;
//            if ($turnover > 0) {
//                //完款比例
//                $data[$k]['payment_ratio']=round(($payment / $turnover) * 100, 2);
//                //利润率
//                $data[$k]['lirun_ratio']=round(($lirun / $turnover) * 100, 2);
//            }

            $ues[$k]['turnover']     =$turnover;
            $ues[$k]['payment']      =$payment;
//            $ues[$k]['increment']    =$increment;
//            $ues[$k]['payment_ratio']=$data[$k]['payment_ratio'] . '%';
//            $ues[$k]['artificial']   =$artificial;
//            $ues[$k]['material']     =$material;
//            $ues[$k]['outlay']       =$outlay;
//            $ues[$k]['lirun']        =$lirun;
//            $ues[$k]['lirun_ratio']  =$data[$k]['lirun_ratio'];
        };
       if(!empty($ues)){
           vendor('phpcvs.Csv');
           $csv=new Csv();
           $csv->export($ues, $xlsCell);
           return res_date($ues);
       }
        return '暂无数据';


    }

    public function orderOriginal()
    {
        $data     = Request::instance()->get();
       
        $relation = db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node     = db('role')->where('id', $relation['role_id'])->find();
        $xlsCell  = ['ID', '渠道一级', '渠道二级', '订单号', '一级类别', '二级类别', '联系人', '联系方式', '地址', '备注', '订单状态', '取消原因', '无效原因', '下单时间', '预约上门时间', '上门时间','精装房','是否有效', '总价', '接单人', '店铺', '打分', '满意度', '内容', '不满意', '最好的地方', '核算成本', '报价金额', '推荐人', '小区名称', '所属物业'];
        $xlsData  = db('order')
            ->alias('a')
            ->field('a.*,b.title,us.username as namekl,us.ce,st.store_name,co.type,co.content,cy.residential_id,cy.management,group.username as  content1,goo.title as problemTitle, goo1.title as problemTitle1,concat(p.province,c.city,u.county,a.addres) as  address,channel_details.title as channel_detailsTitle,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')
            ->join('chanel b', 'a.channel_id=b.id', 'left')
            ->join('channel_details', 'a.channel_details=channel_details.id', 'left')
            ->join('goods_category goo', 'a.pro_id=goo.id', 'left')
            ->join('goods_category goo1', 'a.pro_id1=goo1.id', 'left')
            ->join('province p', 'a.province_id=p.province_id', 'left')
            ->join('city c', 'a.city_id=c.city_id', 'left')
            ->join('county u', 'a.county_id=u.county_id', 'left')
            ->join('personal personal', 'a.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('user us', 'a.assignor=us.user_id', 'left')
            ->join('store st', 'st.store_id=us.store_id', 'left')
            ->join('community cy', 'a.order_id=cy.id', 'left')
            ->join('group_community group', 'a.group_community=group.group_id', 'left')
            ->join('cost co', 'co.order_id=a.order_id', 'left');
        if (isset($data['status']) && $data['status'] != '') {

            if ($data['status'] == 20) {
                $xlsData->where(['a.state' => 0]);
            } elseif ($data['status'] == 6) {
                $xlsData->where(['a.state' => 7, 'a.product_id' => ['<>', 0]]);
            } elseif ($data['status'] == 7) {
                $xlsData->where(['a.state' => $data['status'], 'a.product_id' => ['EQ', 0]]);
            } elseif ($data['status'] == 5) {
                $xlsData->where(['a.state' => ['between', [4, 5]]])->order('a.order_id desc');
            } else {
                $xlsData->where(['a.state' => $data['status']])->order('a.order_id desc');
            }
            if ($data['status'] != 0 && $data['status'] != 20) {
                $xlsData->where(['us.ce' => ['<>', 2]]);
            }
        }
        if (!empty($data['addres'])) {
            $xlsData->where(['a.addres' => ['like', "%{$data['addres']}%"]]);

        }
        if (!empty($data['residential_id']) && !empty($data['residential_id'])) {
            $xlsData->where(['group.username|group_community.username' => ['like', "%{$data['residential_id']}%"]]);

        }
        if (!empty($data['city_id'])) {

            $xlsData->where(['a.city_id' => $data['city_id']]);
        }
        if (isset($data['management']) && $data['management'] != '') {

            $xlsData->where(['cy.management|personal.username' => ['like', "%{$data['management']}%"]]);
        }
        if ($node['rule'] != '*') {

            if (!empty($this->admin['store_id'])) {
                $xlsData->where(['us.store_id' => $this->admin['store_id']]);
            }
            if (!empty($this->admin['visit'])) {
                $xlsData->where(['a.created_time' => ['<', $this->admin['visit']]]);
            }
            if (!empty($this->admin['qu'])) {
                $xlsData->where(['a.channel_id' => ['in', $this->admin['qu']]]);
            }
            if (!empty($this->admin['residential_id'])) {
                $xlsData->whereIn('a.group_community|group_community.group_id', $this->admin['residential_id']);
            }else{
                $node['role_name']='我'.$node['role_name'];
                if(strpos($node['role_name'],'物业')>=0 && strpos($node['role_name'],'物业')){
                $xlsData->where('a.group_community','0000001');
                }
            }
        }
        if (isset($data['start_time']) && $data['start_time'] != '') {

            $xlsData->where(['a.created_time' => ['>=', strtotime($data['start_time'])]]);
        }
        if (isset($data['end_time']) && $data['end_time'] != '') {

            $xlsData->where(['a.created_time' => ['<=', strtotime($data['end_time']) + 24 * 3600]]);
        }

        if (isset($data['contacts']) && $data['contacts'] != '' && $data['chen'] != '' && $data['chen']) {

            $xlsData->where(['a.' . $data['chen'] . '' => ['like', "%{$data['contacts']}%"]]);
        };
        if (isset($data['channel_id']) && $data['channel_id'] != '') {

            $xlsData->where(['a.channel_id' => $data['channel_id']]);

        };
        if (isset($data['full']) && $data['full'] != '') {
            $product = db('product')->where(['full' => ['like', "%{$data['full']}%"]])->distinct(true)->column('orders_id');

            $xlsData->whereIn('a.order_id', $product);

        };
        if (isset($data['store_id']) && $data['store_id'] != '') {

            $xlsData->where(['us.store_id' => $data['store_id']]);


        };
        if (isset($data['assignor']) && $data['assignor'] != '') {
            $xlsData->where(['us.username' => ['like', "%{$data['assignor']}%"]]);
        };

        if (isset($data['remote_time']) && $data['remote_time'] != '') {
            $ytime  = strtotime(date("Y-m-d", strtotime($data['remote_time'])));//昨天开始时间戳
            $yetime = $ytime + 24 * 60 * 60 - 1;//昨天结束时间戳
            $title  = db('through')->where(['end_time' => ['between', [$ytime, $yetime]]])->field('order_ids')->select();
            $s      = '';
            foreach ($title as $key => $val) {
                $s .= $val['order_ids'] . ',';
            }
            $s    = substr($s, 0, -1);
            $arr  = explode(',', $s);
            $arr  = array_unique($arr);//内置数组去重算法
            $data = implode(',', $arr);
            $data = trim($data, ',');
            if ($data) {
                $xlsData->where(['a.order_id' => ['in', $data]]);
            }

        };
        if (isset($data['remote']) && $data['remote'] != '') {
            $title = db('through')->where(['status' => ['like', "%{$data['remote']}%"]])->field('order_ids')->order('through_id desc')->select();
            $s     = '';
            foreach ($title as $key => $val) {
                $s .= $val["order_ids"] . ',';
            }
            $s    = substr($s, 0, -1);
            $arr  = explode(',', $s);
            $arr  = array_unique($arr);//内置数组去重算法
            $data = implode(',', $arr);
            $data = trim($data, ',');

            if ($data) {
                $xlsData->where(['a.order_id' => ['in', $data]]);
            }

        };

        $ue = $xlsData->order('a.created_time desc')->select();;
   
        foreach ($ue as $k => $v) {
            $ues[$k]['order_id']        = $v['order_id'];
            $ues[$k]['channel_id']      = $v['title'];
            $ues[$k]['channel_details'] = $v['channel_detailsTitle'];
            $ues[$k]['order_no']        = $v['order_no'] . "\t";
            $ues[$k]['pro_id']          = $v['problemTitle'];
            $ues[$k]['pro_id1']         = $v['problemTitle1'];
            $ues[$k]['contacts']        = $v['contacts'];
            $ues[$k]['telephone']       = $v['telephone'] . "\t";
            $ues[$k]['addres']          = $v['address'];
            $ues[$k]['remarks']         = $v['remarks'];
            if ($v['state'] == 7 && $v['product_id'] == 0) {
                $o = "已完工";
            } elseif ($v['state'] == 7 && $v['product_id'] != 0) {
                $o = "已完成";
            } else {
                $o = OrderModel::type($v['state']);
            }
               
            $ues[$k]['states'] = $o;
            $ues[$k]['reason'] = $v['reason'];
            $ues[$k]['wu']     = $v['state'] != 10 ? '' : Db::connect(config('database.cc'))->table('invalid')->where('order_id', $v['order_id'])->field('concat(reason,"-",notereason) as reasons')->find()['reasons'];;

            $ues[$k]['created_time'] = date('Y-m-d H:i:s', $v['created_time']) . "\t";
            $punch_clock=db('clock_in')->where('order_id', $v['order_id'])->value('punch_clock');
 
            if ($v['planned']) {
                $ues[$k]['planned'] = date('Y-m-d H:i:s', $v['planned']) . "\t";
            } else {
                $ues[$k]['planned'] = '';
            }
  
            $ues[$k]['punch_clock'] =empty($punch_clock)?'':$punch_clock. "\t";
  
            $ues[$k]['hardbound'] =empty($v['hardbound'])?'不是':'是';
            if ($v['state'] != 10) {
                $ues[$k]['you'] = "有效";
            } else {
                $ues[$k]['you'] = "无效";
            }
              
            if ($v['state'] > 3 && $v['state'] != 10) {
                $turnover0 = (new OrderModel())->newOffer($v['order_id']);
                $amount    = $turnover0['amount'];
            } else {
                $amount = 0;
            }
              
            $ues[$k]['turnover']   = $amount;
            $ues[$k]['namekl']     = $v['namekl'];
            $ues[$k]['store_name'] = $v['store_name'];

            $product                 = db('product')->where(['orders_id' => $v['order_id']])->field('score,full,content as content3,dissatisfied,best')->order('product_id desc')->find();
            $ues[$k]['score']        = $product['score'];
            $ues[$k]['full']         = $product['full'];
            $ues[$k]['content3']     = $product['content3'];
            $ues[$k]['dissatisfied'] = $product['dissatisfied'];
            $ues[$k]['best']         = $product['best'];
            if ($v['type'] == 1) {
                $ues[$k]['type'] = '是';
            } elseif ($v['type'] == 5) {
                $ues[$k]['type'] = '否';
            } else {
                $ues[$k]['type'] = '待定';
            }
            $ues[$k]['content'] = $v['content'];

            if ($v['channel_id'] == 35 && ($v['channel_details'] == 118 || $v['channel_details'] == 120|| $v['channel_details'] == 119) && $v['tui_jian'] != 0) {
                $ues[$k]['management']           = $v['username2'];
                $ues[$k]['residential_quarters'] = $v['usernames1'];
                $ues[$k]['content1']             = $v['usernames'];

            } else {
                $ues[$k]['management']           = $v['management'];
                $ues[$k]['residential_quarters'] = $v['residential_quarters'];
                $ues[$k]['content1']             = $v['content1'];
            }

            $through = db('through')->where(['order_ids' => $v['order_id']])->order('th_time desc')->limit(0, 3)->select();
            foreach ($through as $l => $value) {
                if ($value['role'] == 1) {
                    $ues[$k]['roles' . $l] = '客服';
                } elseif ($value['role'] == 2) {
                    $ues[$k]['roles' . $l] = '店长';
                } else {
                    $ues[$k]['roles' . $l] = '师傅';
                }
                if ($value['role'] == 1) {
                    $ues[$k]['username' . $l] = db('admin')->where('admin_id', $value['admin_id'])->value('username');
                } elseif ($value['role'] == 2) {
                    $ues[$k]['username' . $l] = db('user')->where('user_id', $value['admin_id'])->value('username');
                } else {
                    $ues[$k]['username' . $l] = Db::connect(config('database.db2'))->table('app_user')->where('id', $value['gid'])->value('username');
                }
                $ues[$k]['remar' . $l]   = $value['remar'];
                $ues[$k]['th_time' . $l] = date('Y-m-d H:i:s', $value['th_time']) . "\t";;

            }
            unset($ue[$k]);


        }

        $user = $this->check_authority();
        $user = ['admin_id' => $user['admin_id'], 'created_at' => time(), 'content' => '下载了订单记录', 'ord_id' => '',

        ];
        db('log')->insertGetId($user);
        $po=[];
        for ($i=0; $i < 2; $i ++) {
            $po[$i]['title'] ='分类';
            $po[$i]['name']  ='姓名';
            $po[$i]['remake']='备注';
            $po[$i]['time']  ='跟进时间';
        }



        if(!empty($ues)){
            vendor('phpcvs.Csv');
            $csv=new Csv();
            $csv->export($ues, $xlsCell);
            return res_date($ues);
        }
        return '暂无数据';
    }

    /*
    * 社区关联情况分析
    */
    public function associatedCommunities()
    {

        $xlsCell=['时间', '城市', '所属物业', '社区', '一级SKU', '订单量', '一级SKU占比', '上门量', '上门率', '成交量', '上门成交率', '成交额', '成交占比', '客单价',];
        $data=Request::instance()->get();
        $relation=db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node    =db('role')->where('id', $relation['role_id'])->find();
        $mp     =db('order')
            ->join('city', 'order.city_id=city.city_id', 'left')
            ->join('goods_category', 'order.pro_id=goods_category.id', 'left')
            ->join('community cy', 'order.order_id=cy.id', 'left')
            ->join('personal personal', 'order.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personal.num=personals.personal_id', 'left')
            ->join('user us', 'order.assignor=us.user_id', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join('residential', 'residential.residential_id=cy.residential_id', 'left')
            ->field('count(*) as count,FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,city.city,order.city_id,goods_category.title,residential.content,order.residential_quarters,order.pro_id,personal.username as username2,group_community.username as usernames,group_community1.username as usernames1')
            ->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),order.city_id,cy.residential_id,order.residential_quarters,order.pro_id");
        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start=strtotime($data['start_time'] . ' 00:00:00');
            $end  =strtotime($data['end_time'] . ' 23:59:59');
            $mp->whereBetween('created_time', [$start, $end]);
        }
        if (!empty($data['residential_id']) && !empty($data['residential_id'])) {
            $mp->where(['residential.content|group_community.username'=>['like', "%{$data['residential_id']}%"]]);
        }
        if (!empty($data['residential_quarters']) && !empty($data['residential_quarters'])) {
            $mp->where('order.residential_quarters', $data['residential_quarters']);
        }
        if (!empty($data['city_id']) && !empty($data['city_id'])) {
            $mp->where('order.city_id', intval($data['city_id']));
        }
        if (!empty($data['pro_id']) && !empty($data['pro_id'])) {
            $mp->where('order.pro_id', intval($data['pro_id']));
        }
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $mp->where(['us.store_id'=>$this->admin['store_id']]);
            }

            if (!empty($this->admin['qu'])) {
                $mp->whereIn('order.channel_id', $this->admin['qu']);
            }

            if (!empty($this->admin['residential_id'])) {
                $mp->whereIn('cy.residential_id', $this->admin['residential_id']);
            }

        }
        $list=$mp->select();
        foreach ($list as $key=>$item) {
            $todayStart   =strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd     =strtotime($item['posttime'] . ' 23:59:59');
            //线索量
            $order                            =db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id'=>$item['city_id'], 'residential_quarters'=>$item['residential_quarters'], 'pro_id'=>$item['pro_id']])->field('city_id,channel_id,planned,state')->count();
            $OrderQuantity                    =db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id'=>$item['city_id'], 'residential_quarters'=>$item['residential_quarters'], 'pro_id'=>$item['pro_id'], 'state'=>['neq', 10]])->count();
            $deal                             =db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id'=>$item['city_id'], 'residential_quarters'=>$item['residential_quarters'], 'pro_id'=>$item['pro_id']])->where('planned', 'neq', 0)->count();
            $ues[$key]['posttime']            =$item['posttime'] . "\t";
            $ues[$key]['city']                =$item['city'];
            $ues[$key]['content']             =$item['content'];
            $ues[$key]['residential_quarters']=$item['residential_quarters'];
            $ues[$key]['title']               =$item['title'];
            $ues[$key]['order']               =$order;
            $ues[$key]['Efficiency']          =sprintf("%.2f", $OrderQuantity / $order);
            $ues[$key]['dealCount']           =$deal;
            $ues[$key]['doorToDoorRate']      =empty($OrderQuantity)? 0 : sprintf("%.2f", $deal / $OrderQuantity);
            $signUp                           =db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id'=>$item['city_id'], 'order.residential_quarters'=>$item['residential_quarters'], 'order.pro_id'=>$item['pro_id']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            $ues[$key]['signUp']              =count($signUp);

            $ues[$key]['doorToDoorTransactionRate']=empty($deal)? 0 : sprintf("%.2f", $ues[$key]['signUp'] / $deal);
            if (!empty($signUp)) {
                $amount=0;
                foreach ($signUp as $o) {
                    $turnover0=(new OrderModel())->newOffer($o);
                    $amount   +=$turnover0['amount'];
                }
            } else{
                $amount=0;
            }
            $ues[$key]['turnover']         =$amount;
            $ues[$key]['orderTurnoverRate']=empty($OrderQuantity)? 0 : sprintf("%.2f", $ues[$key]['signUp'] / $OrderQuantity);
            $ues[$key]['turnoverS']        =empty($ues[$key]['signUp'])? 0 : sprintf("%.2f", $amount / $ues[$key]['signUp']);

        }
        vendor('phpcvs.Csv');
        $csv=new Csv();
        $csv->export($ues, $xlsCell);

        return res_date($ues);
    }

    public function cityStoreManagerReceivesOrders()
    {
        $xlsCell=['时间','城市','门店', '店长', '接单量', '上门量', '上门率', '远程沟通', '远程沟通率', '成交量', '成交额', '上门成交率', '订单成交率', '客单价',];
        $relation=db('relation')->where('user_id', $this->admin['admin_id'])->find();
        $node    =db('role')->where('id', $relation['role_id'])->find();
        $mp=db('order')
            ->join('city', 'order.city_id=city.city_id', 'left')
            ->join('user', 'order.assignor=user.user_id', 'left')
            ->join('store', 'user.store_id=store.store_id', 'left');

        if (!empty($data['start_time']) && !empty($data['end_time'])) {
            $start=strtotime($data['start_time'] . ' 00:00:00');
            $end  =strtotime($data['end_time'] . ' 23:59:59');

            $mp->whereBetween('order.created_time', [$start, $end]);
        }
        if (!empty($data['city_id']) && !empty($data['city_id'])) {

            $mp->where('order.city_id', $data['city_id']);
        }
        if (!empty($data['user_id']) && !empty($data['user_id'])) {

            $mp->where('order.assignor', $data['user_id']);
        }
        if (!empty($data['store_id']) && !empty($data['store_id'])) {

            $mp->where('user.store_id', $data['store_id']);
        }
        if ($node['rule'] != '*') {
            if (!empty($this->admin['store_id'])) {
                $mp->where(['us.store_id'=>$this->admin['store_id']]);
            }

            if (!empty($this->admin['qu'])) {
                $mp->whereIn('order.channel_id', $this->admin['qu']);
            }

            if (!empty($this->admin['residential_id'])) {
                $mp->whereIn('cy.residential_id', $this->admin['residential_id']);
            }

        }
        $list=$mp->field('count(*) as count,FROM_UNIXTIME(order.created_time,"%Y-%m-%d") as posttime,city.city,order.assignor,order.city_id,store.store_name,user.username')
            ->group("FROM_UNIXTIME(order.created_time,'%Y-%m-%d'),city.city,order.assignor")->where('order.state', 'neq', 10)->order('posttime desc')->select();
        foreach ($list as $key=>$item) {
            $todayStart=strtotime($item['posttime'] . ' 00:00:00'); //2016-11-01 00:00:00
            $todayEnd  =strtotime($item['posttime'] . ' 23:59:59');
            //签约总业绩
            $signUp=db('order')->join('contract', 'order.contract_id=contract.contract_id', 'left')->whereBetween('contract.con_time', [$todayStart, $todayEnd])->where(['order.city_id'=>$item['city_id'], 'order.assignor'=>$item['assignor']])->whereBetween('order.state', [4, 7])->column('order.order_id');
            if (!empty($signUp)) {
                $amount=0;
                foreach ($signUp as $o) {
                    $turnover0=(new OrderModel())->newOffer($o);
                    $amount   +=$turnover0['amount'];
                }
            } else{
                $amount=0;
            }
            //接单量
            $order=db('order')->whereBetween('created_time', [$todayStart, $todayEnd])->where(['city_id'=>$item['city_id'], 'order.assignor'=>$item['assignor']])->count();
            $ues[$key]['posttime']=$item['posttime'];
            $ues[$key]['city']=$item['city'];
            $ues[$key]['store_name']=$item['store_name'];
            $ues[$key]['username']=$item['username'];
            $ues[$key]['order']=$order;
            //上门量
            $deal=db('order')->whereBetween('planned', [$todayStart, $todayEnd])->where(['city_id'=>$item['city_id'], 'order.assignor'=>$item['assignor']])->count();
            //远程沟通
            $through          =db('through')->where(['admin_id'=>$item['assignor']])->whereBetween('th_time', [$todayStart, $todayEnd])->count();

            $ues[$key]['dealCount']=$deal;

            $ues[$key]['doorToDoorRate']           =empty($order)? 0 : sprintf("%.2f", $deal / $order);
            $ues[$key]['through']                  =$through;
            $ues[$key]['throughCount']             =sprintf("%.2f", $through / $order);
            $ues[$key]['signUp']                   =count($signUp);
            $ues[$key]['turnover']                 =$amount;
            $ues[$key]['doorToDoorTransactionRate']=empty($deal)? 0 : sprintf("%.2f", count($signUp) / $deal);
            $ues[$key]['orderTurnoverRate']        =empty($deal)? 0 : sprintf("%.2f", count($signUp) / $order);
            $ues[$key]['turnoverS']                =empty($item['signUp'])? 0 : sprintf("%.2f", $amount / $item['signUp']);


        }


        vendor('phpcvs.Csv');
        $csv=new Csv();
        $csv->export($ues, $xlsCell);
        return res_date($ues);
    }

    public function ss()
    {
        $xlsCell=['物业','小区','用户姓名', '公司的运营负责人'];

        $list=db('personal')->join('group_community', 'group_community.group_id=personal.residential_quarters', 'left')->field('personal.username,group_community.username as username1,group_community.charge')->select();

        foreach ($list as $key=>$item) {
            if(!empty($item['num'])){
                $group_id=db('personal') ->where('personal_id',$item['num'])->value('group_id');
                $group_community=db('group_community') ->where('group_community.group_id',$group_id)->find();
            }elseif( $item['group_id']==1){
                $group_community=db('group_community') ->where('group_community.group_id',$item['group_id'])->find();
            }else{
                $group_community['username']='';
            }
            $user[$key]['wuye']=$group_community['username'];
            $user[$key]['xiaoqu']=$item['username1'];
            $user[$key]['yonghu']=$item['username'];
            $user[$key]['charge']=$item['charge'];

        }

        vendor('phpcvs.Csv');
        $csv=new Csv();
        $csv->export($user, $xlsCell);
        return res_date($user);
    }

}